###########################################################################
# $Id: conceptStorageSQL.p 51 2009-07-15 09:14:52Z donbeave $
###########################################################################

@CLASS
conceptStorageSQL

@USE
pf/collections/pfArrayList.p
concept/storages/conceptStorage.p

@BASE
conceptStorage

@OPTIONS
locals

#----- Constructor -----

@create[hshOptions]
## hshOptions.sql
## hshOptions.sqlConnectString[$MAIN:SQL.connect-string]
## hshOptions.sqlOptions
## hshOptions.tablesPrefix
  ^BASE:create[$hshOptions]

  ^cleanMethodArgument[hshOptions]

  $_createOptions[$hshOptions]

  ^if(def $hshOptions.sql && $hshOptions.sql is pfSQL){
    $self._sql[$hshOptions.sql]
  }{
     $self._sql[]
   }

  ^if(def $hshOptions.tablesPrefix){
    $self._tablesPrefix[^hshOptions.tablesPrefix.trim[]]
  }

  $self._tables[
    $.Locales[${_tablesPrefix}locales]
    $.Objects[${_tablesPrefix}objects]
    $.ObjectsTypes[${_tablesPrefix}objects_types]
    $.ObjectsTexts[${_tablesPrefix}objects_texts]
    $.DataTypes[${_tablesPrefix}data_types]
    $.Blocks[${_tablesPrefix}blocks]
    $.Blocks_to_Objects[${_tablesPrefix}blocks_to_objects]
    $.Processes[${_tablesPrefix}processes]
    $.ProcessesTypes[${_tablesPrefix}processes_types]
  ]

  ^if(^isUseSharedLocales[]){
    $self._tables.Locales[locales]
  }

  $self._additionalFields[
    $.Objects[^table::create{select	update	field}]
    $.ObjectsTexts[^table::create{select	update	field}]
  ]

  $self._localesList[]
  $self._objectsTypesList[]
  $self._dataTypesList[]

  $self._objectsHash[^hash::create[]]
  $self._objectsTextsHash[^hash::create[]]
  $self._objectsBlocksHash[^hash::create[]]

  $self._isObjectBlockLoaded[^hash::create[]]

  $self._preloadBlocks(false)

#----- Public -----

@addField[strTableName;strFieldName;strSelectColumn;strUpdateColumn]
  ^labourAssert:required[strTableName;string]
  ^labourAssert:required[strFieldName;string]

  $tFields[$_additionalFields.[$strTableName]]
  $sSelectColumn[^if(def $strSelectColumn){$strSelectColumn}{$strFieldName}]
  $sUpdateColumn[^if(def $strUpdateColumn){$strUpdateColumn}{$sSelectColumn}]

# добавляем поле (если нет совпадениея с именем уже добавленного поля)
  ^if(!^tFields.locate[select;$sSelectColumn]){
    ^tFields.append{$sSelectColumn	$sUpdateColumn	$strFieldName}
  }

@setLocalizedData[objItem;altData;altLocale]
  ^labourAssert:required[objItem;conceptObjectItem]
  ^pfAssert:isTrue($altData is string || $altData is hash)[Data is wrong (CLASS_NAME: $altData.CLASS_NAME).]

  $oLocale[$defaultLocale]

  ^if(def $altLocale){
    ^if($altLocale is string){
      $oLocale[^getLocaleByLanguage[$altLocale]]
    }{
       ^labourAssert:required[altLocale;conceptLocaleItem]
       $oLocale[$altLocale]
     }
  }

  $result[]

  $sID[^if(^objItem.isExist[]){$objItem.ID}{$objItem.UID}]

  $oText[$self._objectsTextsHash.[$sID].[$oLocale.ID]]

  ^if($oText is conceptObjectTextItem){
    ^if($altData is string){
      ^oText.set[Name;$altData]
    }{
       ^altData.foreach[sFieldName;aValue]{
         ^oText.set[$sFieldName;$aValue]
       }
     }
  }{
     $oText[^createObjectText[$altData;$objItem;$oLocale]]

#    добавляем только локализацию для объекта в хэш
     $self._objectsTextsHash.[$sID].[$oLocale.ID][$oText]
   }

@loadLocales[]
  $result[]
  $tItems[^_getLocalesTable[]]
  $self._localesList[^_makeList[$tItems;Locale]]
  $self._isLocalesLoaded(true)

@loadObjectsTypes[]
  $result[]
  $tItems[^_getObjectsTypesTable[]]
  $self._objectsTypesList[^_makeList[$tItems;ObjectType]]
  $self._isObjectsTypesLoaded(true)

@loadDataTypes[]
  $result[]
  $tItems[^_getDataTypesTable[]]
  $self._dataTypesList[^_makeList[$tItems;DataType]]
  $self._isDataTypesLoaded(true)


@getLocales[]
  ^if(!$self._isLocalesLoaded){
    ^loadLocales[]
  }
  $result[$self._localesList]

@getLocaleByID[intID]
  ^labourAssert:required[intID;int]

  $result[]

  $oLocalesList[^getLocales[]]

  ^oLocalesList.foreach[oLocale]{
    ^if($oLocale.ID == ^intID.int(0)){
      $result[$oLocale]
    }
  }

  ^pfAssert:isTrue(def $result)[Locale not found (ID: $intID).]

@getLocaleByLanguage[strLang]
  ^labourAssert:required[strLang;string]

  $result[]

  $oLocalesList[^getLocales[]]

  ^oLocalesList.foreach[oLocale]{
    ^if($oLocale.Language eq $strLang){
      $result[$oLocale]
    }
  }

  ^pfAssert:isTrue(def $result)[Locale not found (Language: $strLang).]

@getTemporaryObject[strUID]
  ^pfAssert:isTrue(def $strUID && $strUID is string)["UID" is not specified or wrong.]

  $result[$self._objectsHash.[$strUID]]

  ^pfAssert:isTrue(def $result)[Object not found (UID: $strUID).]

@getRootObjects[hshOptions]
  $hshOptions[^hash::create[$hshOptions]]
  $hshOptions.filter[^hash::create[$hshOptions.filter]]
  $hshOptions.filter.onlyRoot(true)
  $result[^_getObjects[$hshOptions]]

@getObjectByID[intID]
  ^labourAssert:required[intID;int]

  $oItems[^_getObjects[
    $.filter[
      $.ID(^intID.int(0))
    ]
  ]]

  ^if($oItems){
    $result[$oItems.currentItem]
  }

  ^pfAssert:isTrue(def $result)[Object not found (ID: $intID).]

@getObjectsByType[objType;hshOptions]
  ^labourAssert:required[objType;conceptObjectTypeItem]

  $hshOptions[^hash::create[$hshOptions]]
  $hshOptions.filter[^hash::create[$hshOptions.filter]]
  $hshOptions.filter.TypeID(^objType.ID.int(0))
  $result[^_getObjects[$hshOptions]]

  ^pfAssert:isTrue(def $result)[Objects not found (TypeID: $objType.ID).]

@getObjectChildren[objItem;hshOptions]
  ^labourAssert:required[objItem;conceptObjectItem]

  $hshOptions[^hash::create[$hshOptions]]
  $hshOptions.filter[^hash::create[$hshOptions.filter]]
  $hshOptions.filter.ParentID(^objItem.ID.int(0))
  $result[^_getObjects[$hshOptions]]

  ^pfAssert:isTrue(def $result)[Object's children not found (ID: $objItem.ID).]

@getObjectChildrenByName[objItem;strName;objLocale]
  ^labourAssert:required[objItem;conceptObjectItem]

# TODO возможно сделать оптимизацию
  $result[^pfList::create[]]

  $oLocale[^if($objLocale is conceptLocaleItem){$objLocale}{$defaultLocale}]

  $oChilds[$objItem.children]

  ^oChilds.foreach[oChild]{
    ^if(^oChild.hasData[$oLocale] && $oChild.[$oLocale.Language].Name eq $strName){
      ^result.add[$oChild]
    }
  }

@getObjectParent[objItem]
  ^labourAssert:required[objItem;conceptObjectItem]

  ^if(def $objItem.ParentUID){
    $result[^getTemporaryObject[$objItem.ParentUID]]
  }{
     $result[^getObjectByID(^objItem.parentID.int(0))]
   }

@loadObjectsLocalizedData[altData]
  ^if($altData is pfArrayList){
    $sIDs[^altData.foreach[oItem]{$oItem.ID}[,]]

    $tItems[^_getObjectsTextsTable[
      $.filter[
        $.IDs[$sIDs]
      ]
    ]]

    ^if($tItems){
      ^tItems.menu{
        $oText[^_createObjectTextItem[$tItems.fields]]
        $oText._isExist(true)
      }
    }
  }
  $result[]

@getObjectLocalizedData[objItem;altLocale]
  ^labourAssert:required[objItem;conceptObjectItem]

  $sID[^if(^objItem.isExist[]){$objItem.ID}{$objItem.UID}]

  ^if($altLocale is string){
    $oLocale[^getLocaleByLanguage[$altLocale]]
  }{
     $oLocale[^if($altLocale is conceptLocaleItem){$altLocale}{$defaultLocale}]
   }

  $result[$self._objectsTextsHash.[$sID].[$oLocale.ID]]

  ^pfAssert:isTrue(def $result)[Object's localized data not found (ObjectID: $objItem.ID, LocaleID: $objLocale.ID).]

@hasObjectLocalizedData[objItem;objLocale]
  ^labourAssert:required[objLocale;conceptLocaleItem]

  $sID[^if(^objItem.isExist[]){$objItem.ID}{$objItem.UID}]

  ^if(def $self._objectsTextsHash.[$sID].[$objLocale.ID]){
    $result(true)
  }{
     $result(false)
   }

@getObjectTexts[objItem]
  ^labourAssert:required[objItem;conceptObjectItem]

  $sID[^if(^objItem.isExist[]){$objItem.ID}{$objItem.UID}]
  $result[^hash::create[$self._objectsTextsHash.[$sID]]]

@getObjectsTypes[]
  ^if(!$_isObjectsTypesLoaded){
    ^loadObjectsTypes[]
  }
  $result[$_objectsTypesList]

@getObjectTypeByID[intID]
  ^labourAssert:required[intID;int]

  $result[]
  ^if(!$_isObjectsTypesLoaded){
    ^loadObjectsTypes[]
  }
  ^_objectsTypesList.foreach[oType]{
    ^if($oType.ID == ^intID.int(0)){
      $result[$oType]
    }
  }

  ^pfAssert:isTrue(def $result)[Type not found (ID: $intID).]

@getObjectTypeByName[strName]
  ^labourAssert:required[strName;string]

  $result[]
  ^if(!$_isObjectsTypesLoaded){
    ^loadObjectsTypes[]
  }
  ^_objectsTypesList.foreach[oType]{
    ^if($oType.Name eq $strName){
      $result[$oType]
    }
  }

  ^pfAssert:isTrue(def $result)[Type not found (Name: $strName).]

@getBlockByID[intID]
  ^labourAssert:required[intID;int]

  $oItems[^getBlocks[
    $.filter[
      $.ID(^intID.int(0))
    ]
  ]]
  ^if($oItems){
    $result[$oItems.currentItem]
  }{
     $result[]
   }

  ^pfAssert:isTrue(def $result)[Block not found (ID: $intID).]

@getBlocksByObjectID[intObjectID;hshOptions]
## hshOptions.filter.parentBlockID
  ^labourAssert:required[intObjectID;int]

  ^cleanMethodArgument[hshOptions]

  ^if(^isTreeBlocksSupported[]){
    ^if(!^isObjectBlocksLoaded[^intObjectID.int(0)]){
      ^loadObjectBlocks[^intObjectID.int(0)]
    }
    $result[$self._objectsBlocksHash.[^intObjectID.int(0)].[^hshOptions.filter.parentBlockID.int(0)]]]
  }{
    $result[^_getBlocks[
      $.filter[
        $.objectID(^intObjectID.int(0))
      ]
    ]]
  }

  ^pfAssert:isTrue(def $result)[Blocks not found (ObjectID: $intObjectID).]

@getSharedBlocks[hshOptions]
  ^cleanMethodArgument[hshOptions]

  $hshOptions.filter[^hash::create[$hshOptions.filter]]
  $hshOptions.filter.onlyShared(true)

  $result[^_getBlocks[$hshOptions]]

@getAllBlockChilds[intBlockID;intObjectID]
  ^labourAssert:required[intBlockID;int]
  ^labourAssert:required[intObjectID;int]

  $result[^pfArrayList::create[]]

  ^if(^isTreeBlocksSupported[]){
    ^if(!^isObjectBlocksLoaded[^intObjectID.int(0)]){
      ^loadObjectBlocks[^intObjectID.int(0)]
    }

    $result[^_getBlockChilds[^intBlockID.int(0);^intObjectID.int(0)]]
  }

@loadObjectBlocks[intObjectID]
# сначало загружаем все блоки объекта
  $tItems[^_getBlocksTable[
    $.filter[
      $.ObjectID(^intObjectID.int(0))
    ]
  ]]

  $hBlocksByObject[^tItems.hash[objectID;
    $.type[table]
    $.distinct(true)
  ]]

# перебираем все загруженные блоки и добавляем их в hash
  ^hBlocksByObject.foreach[iObjectID;tObjectBlocks]{
    $_objectsBlocksHash.[^iObjectID.int(0)][^hash::create[]]

#   если у загруженного объекта имеются блоки, то добавляем их в ArrayList
    ^if($tObjectBlocks){
      $hBlocksByParentID[^tObjectBlocks.hash[parentBlockID;
        $.type[table]
        $.distinct(true)
      ]]

      ^hBlocksByParentID.foreach[iParentID;tBlocks]{
        $_objectsBlocksHash.[^iObjectID.int(0)].[^iParentID.int(0)][^pfArrayList::create[]]

        ^if($tBlocks){
          ^tBlocks.menu{
            $oBlockItem[^_createBlockItem[$tBlocks.fields]]

            ^_objectsBlocksHash.[^iObjectID.int(0)].[^iParentID.int(0)].add[$oBlockItem]
          }
        }
      }
    }
  }

  $_isObjectBlockLoaded.[^intObjectID.int(0)](true)

@loadObjectsBlocks[objList]
  ^labourAssert:required[objList;pfList]

  ^objList.foreach[oItem]{
    ^loadObjectBlocks[$oItem.ID]
  }

@isObjectBlocksLoaded[intObjectID]
  $result(^_isObjectBlockLoaded.[^intObjectID.int(0)].bool(false))

@getDataTypes[]
  ^if(!$_isDataTypesLoaded){
    ^loadDataTypes[]
  }
  $result[$_dataTypesList]

@getDataTypeByID[intID]
  ^labourAssert:required[intID;int]

  $result[]
  ^if(!$_isDataTypesLoaded){
    ^loadDataTypes[]
  }
  ^_dataTypesList.foreach[oType]{
    ^if($oType.ID == ^intID.int(0)){
      $result[$oType]
    }
  }

  ^pfAssert:isTrue(def $result)[Data type not found (ID: $intID).]

@getProcesses[]
  $tItems[^getProcessesTable[]]
  $result[^_makeList[$tItems;Process]]



@getObjectsTotalCount[hshOptions]
  $result(^CSQL.int{
    SELECT
      COUNT(*)
    FROM
      `$tables.Objects` main,
      `$tables.ObjectsTexts` texts
    WHERE
      main.object_id = texts.object_id AND
      texts.locale_id = ^if(def $defaultLocale){$defaultLocale.ID}{1} AND
      texts.is_published = 1
  }[
    $.default(0)
  ])

@getSharedBlocksCount[hshOptions]
  $result(^CSQL.int{
    SELECT
      COUNT(*)
    FROM
      `$tables.Blocks` main
    WHERE
      main.is_shared = 1
  }[
    $.default(0)
  ])

#----- Private -----

@_makeList[tblItems;strType]
  ^labourAssert:required[tblItems;table]
  ^labourAssert:required[strType;string]

  $result[^pfArrayList::create[]]

  $sCreatorMethod[_create${strType}Item]
  ^if($$sCreatorMethod is junction){
    $jItemCreator[$$sCreatorMethod]
  }

  ^if($tblItems){
    ^tblItems.menu{
      ^if($jItemCreator is junction){
        $oItem[^jItemCreator[$tblItems.fields]]
      }{
        $oItem[^_createBaseItem[$strType;$tblItems.fields]]
      }

      $oItem._isExist(true)

      ^result.add[$oItem]
    }
  }

@_createObjectItem[hshData]
  ^if(^hshData.ID.int(0)){
    ^if(def $self._objectsHash.[^hshData.ID.int(0)]){
      $result[$self._objectsHash.[^hshData.ID.int(0)]]

      ^hshData.foreach[sName;aData]{
        ^result.set[$sName;$aData]
      }
    }{
       $result[^BASE:_createObjectItem[$hshData]]
       $result._isExist(true)
       $self._objectsHash.[$result.ID][$result]
     }
  }{
     $result[^BASE:_createObjectItem[$hshData]]
     $self._objectsHash.[$result.UID][$result]
   }

@_createObjectTextItem[hshData;objItem]
  ^if(^hshData.ObjectID.int(0) && ^hshData.LocaleID.int(0)){
    ^if(def $self._objectsTextsHash.[^hshData.ObjectID.int(0)].[^hshData.LocaleID.int(0)]){
      $result[$self._objectsTextsHash.[^hshData.ObjectID.int(0)].[^hshData.LocaleID.int(0)]]
    }{
       $result[^BASE:_createObjectTextItem[$hshData]]
       $result._isExist(true)
       ^_addTextToHash[$result]
     }
  }{
    ^labourAssert:required[objItem;conceptObjectItem]

     $result[^BASE:_createObjectTextItem[$hshData]]

     ^if(^objItem.isExist[]){
       $result._ObjectID($objItem.ID)
     }{
        $result._ObjectUID[$objItem.UID]
      }

     ^_addTextToHash[$result]
   }

@_addTextToHash[objText]
  $result[]
  $sID[^if(^objText.isExist[]){$objText.ObjectID}{$objText.object.UID}]
  ^if(!($self._objectsTextsHash.[$sID] is hash)){
    $self._objectsTextsHash.[$sID][^hash::create[]]
  }
  $self._objectsTextsHash.[$sID].[$objText.LocaleID][$objText]

@_getLocalesTable[]
  ^CSQL.table{
    SELECT
    main.locale_id AS ID,
    main.sort_order AS SortOrder,
    main.abbr AS Abbr,
    main.language AS Language,
    main.charset AS Charset
  FROM
    `$tables.Locales` main
  }

@_getObjects[hshOptions]
  ^cleanMethodArgument[hshOptions]

  $tItems[^_getObjectsTable[$hshOptions]]

  $result[^_makeList[$tItems;Object]]

  ^if($result){
    ^loadObjectsLocalizedData[$result]

    ^if($preloadBlocks){
      ^loadObjectsBlocks[$result]
    }
  }

@_getObjectsTable[hshOptions]
## hshOptions.filter.ID
## hshOptions.filter.IDs
## hshOptions.filter.ParentID
## hshOptions.filter.TypeID
## hshOptions.filter.onlyRoot

## hshOptions.queryOptions.select
## hshOptions.queryOptions.from
## hshOptions.queryOptions.where

## hshOptions.pagination.limit
## hshOptions.pagination.offset

## hshOptions.force
  ^cleanMethodArgument[hshOptions]

  $hFilter[^hash::create[$hshOptions.filter]]
  $hPagination[^hash::create[$hshOptions.pagination]]
  $hQueryOptions[^hash::create[$hshOptions.queryOptions]]

  ^if(
    !(
      ^hFilter.ID.int(0) ||
      def $hFilter.IDs ||
      ^hFilter.ParentID.int(0) ||
      ^hFilter.TypeID.int(0) ||
      ^hFilter.onlyRoot.bool(false)
    ) &&
    !(^hshOptions.force.bool(false))
  ){
    ^throw[Has not filters applied to query.]
  }

  ^CSQL.table{
    SELECT DISTINCT
      main.object_id AS ID,
      main.sort_order AS SortOrder,
      main.object_process_id AS ProcessID,
      main.object_type_id AS TypeID,
      main.dt_update AS DateUpdate,
      main.parent_id AS ParentID,
      (
        SELECT
          COUNT(*)
        FROM
          `$tables.Objects` c_objects
          ^if(^isReleased[]){
            , `$tables.ObjectsTexts` c_texts
          }
        WHERE
          ^if(^isReleased[]){
            c_objects.object_id = c_texts.object_id AND
            c_texts.locale_id = $defaultLocale.ID AND
            c_texts.is_published = 1 AND
          }
          c_objects.parent_id = main.object_id
      ) AS ChildrenCount

      $tFields[$additionalObjectsFields]

      ^if($tFields){
        ,
        ^tFields.menu{
          $tFields.fields.select ^if(def $tFields.field){AS $tFields.field}
        }[,]
      }

      ^if(def $hshOptions.SELECT){
        , $hshOptions.SELECT
      }
    FROM
      `$tables.Objects` main

      ^if(^isReleased[]){
        , `$tables.ObjectsTexts` texts
      }

      ^if(def $hQueryOptions.from){
        ^if(^hQueryOptions.from.left(4) ne "LEFT"){
          ,
        }
        $hQueryOptions.from
      }
    WHERE
      ^if(^hFilter.ID.int(0)){
        main.object_id = ^hFilter.ID.int(0) AND
      }{
        ^if(def $hFilter.IDs){
          main.object_id IN ($hFilter.IDs) AND
        }
       }

      ^if(^hFilter.ParentID.int(0)){
        main.parent_id = ^hFilter.ParentID.int(0) AND
      }

      ^if(^hFilter.TypeID.int(0)){
        main.object_type_id = ^hFilter.TypeID.int(0) AND
      }

      ^if(^hFilter.onlyRoot.bool(false)){
        main.parent_id = 0 AND
      }

      ^if(^isReleased[]){
        main.object_id = texts.object_id AND

#TODO добавить возможность указания локали
        texts.locale_id = $self.defaultLocale.ID AND
        texts.is_published = 1 AND
      }

      ^if(def $hQueryOptions.where && def ^hQueryOptions.where.trim[]){
        $hQueryOptions.where AND
      }
      1 = 1
    ORDER
      BY main.sort_order DESC
  }[$hPagination]

@_getObjectsTypesTable[]
  ^CSQL.table{
    SELECT
      object_type_id AS ID,
      sort_order AS SortOrder,
      is_fake AS IsFake,
      name AS Name
    FROM
      `$tables.ObjectsTypes` main
  }

@_getObjectsTextsTable[hshOptions]
## hshOptions.filter.ID
## hshOptions.filter.IDs

## hshOptions.force
  ^cleanMethodArgument[hshOptions]

  $hFilter[^hash::create[$hshOptions.filter]]

  ^if(
    !(
      ^hFilter.ID.int(0) ||
      def $hFilter.IDs
    ) &&
    !(^hshOptions.force.bool(false))
  ){
    ^throw[Has not filters applied to query.]
  }

  ^CSQL.table{
    SELECT
      main.locale_id AS LocaleID,
      main.object_id AS ObjectID,
      main.name AS Name,
      main.is_published AS IsPublished

      $tFields[$additionalTextsFields]

      ^if($tFields){
        ,
        ^tFields.menu{
          $tFields.fields.select ^if(def $tFields.field){AS $tFields.field}
        }[,]
      }
    FROM
      `$tables.ObjectsTexts` main
    WHERE
      ^if(^hshOptions.ID.int(0)){
        main.object_id = ^hshOptions.ID.int(0) AND
      }{
        ^if(def $hshOptions.IDs){
          main.object_id IN ($hshOptions.IDs) AND
        }
       }
     ^if(^isReleased[]){
#TODO добавить поддержку специальной локали
        main.is_published = 1 AND
        main.locale_id = $self.defaultLocale.ID AND
      }
      1 = 1
  }

@_getBlocks[hshOptions]
## => @getBlocksTable
  ^cleanMethodArgument[hshOptions]

  ^if(^hshOptions.objectID.int(0) && def $_blocksList.[^hshOptions.objectID.int(0)]){
    $result[$_blocksList.[^hshOptions.objectID.int(0)]]
  }{
     $tItems[^_getBlocksTable[$hshOptions]]

     $result[^_makeList[$tItems;Block]]
   }

@_getBlockChilds[intBlockID;intObjectID;objResult]
  ^if(!($objResult is pfArrayList)){
    $objResult[^pfArrayList::create[]]
  }

  $oList[$_blocksList.[^intObjectID.int(0)].[^intBlockID.int(0)]]

  ^if($oList){
    ^objResult.addRange[$oList]

    ^oList.foreach[n]{
      ^if($_blocksList.[^intObjectID.int(0)].[$oList.currentItem.ID]){
        ^_getBlockChilds[$oList.currentItem.ID;^intObjectID.int(0);$objResult]
      }
    }
  }

  $result[$objResult]

@_getBlocksTable[hshOptions]
## hshOptions.filter.ID
## hshOptions.filter.IDs
## hshOptions.filter.ObjectID
## hshOptions.filter.ObjectIDs
## hshOptions.filter.ParentBlockID
## hshOptions.filter.onlyShared

## hshOptions.queryOptions.select
## hshOptions.queryOptions.from
## hshOptions.queryOptions.where

## hshOptions.pagination.limit
## hshOptions.pagination.offset

## hshOptions.force
  ^cleanMethodArgument[hshOptions]

  $hFilter[^hash::create[$hshOptions.filter]]
  $hPagination[^hash::create[$hshOptions.pagination]]
  $hQueryOptions[^hash::create[$hshOptions.queryOptions]]

  ^pfAssert:isTrue(
    !(
      ^hFilter.ID.int(0) ||
      def $hFilter.IDs ||
      (^hFilter.ObjectID.int(0) || def $hFilter.ObjectsIDs) ||
      ^hFilter.onlyShared.bool(false)
    ) &&
    !(^hshOptions.force.bool(false))
  )[Has not filters applied to query.]

  ^CSQL.table{
    SELECT
      main.block_id AS ID,
      main.data_type_id as DataTypeID,
      main.block_process_id AS ProcessID,
      main.is_not_empty AS IsNotEmpty,
      main.is_published AS IsPublished,
      main.is_shared AS IsShared,
      main.is_parsed_manual AS IsParsedManual,
      main.dt_update AS DateUpdate,
      main.name AS Name,
      main.params AS Params,
      main.description AS Description,
      main.data AS Data
      ^if(^isTreeBlocksSupported[]){
        ,
        (
          SELECT
            COUNT(*)
          FROM
            `$tables.Blocks` c_blocks,
            `$tables.Blocks_to_Objects` c_blocks2objects
          WHERE
            c_blocks.block_id = c_blocks2objects.block_id AND
            c_blocks.is_published = 1 AND
            c_blocks2objects.parent_block_id = main.block_id
        ) AS ChildrenCount
      }
      ^if(^hFilter.ObjectID.int(0) || def $hFilter.ObjectsIDs){
        , `${tables.Blocks_to_Objects}`.object_id AS ObjectID,
        ^if(^isTreeBlocksSupported[]){`${tables.Blocks_to_Objects}`.parent_block_id AS ParentBlockID,}
        `${tables.Blocks_to_Objects}`.sort_order AS SortOrder
      }
      ^if(def $hQueryOptions.select){
        , $hQueryOptions.select
      }
    FROM
      ^if(^hFilter.ObjectID.int(0) || def $hFilter.ObjectsIDs){
        `$tables.Blocks_to_Objects`,
      }
      `$tables.Blocks` main
      ^if(def $hQueryOptions.from){
        , $hQueryOptions.from
      }
    WHERE
      ^if(^hFilter.ID.int(0)){
        main.block_id = ^hFilter.ID.int(0) AND
      }{
         ^if(def $hFilter.IDs){
           main.block_id IN ($hFilter.IDs) AND
         }
       }
      ^if(^hFilter.ObjectID.int(0) || def $hFilter.ObjectsIDs){
        main.block_id = `${tables.Blocks_to_Objects}`.block_id AND

        ^if(def $hFilter.ObjectsIDs){
          `${tables.Blocks_to_Objects}`.object_id IN ($hFilter.ObjectsIDs) AND
        }{
           `${tables.Blocks_to_Objects}`.object_id = ^hFilter.ObjectID.int(0) AND
         }

        ^if(^isTreeBlocksSupported[] && ^hFilter.ParentBlockID.int(0)){
          `${tables.Blocks_to_Objects}`.parent_block_id = ^hFilter.ParentBlockID.int(0) AND
        }
      }
      ^if(^hFilter.onlyShared.bool(false)){
        main.is_shared = 1 AND
      }

      ^if(def $hQueryOptions.where && def ^hQueryOptions.where.trim[]){
        $hQueryOptions.where AND
      }
      1 = 1
  }[$hPagination]

@_getDataTypesTable[]
  ^CSQL.table{
    SELECT
      data_type_id AS ID,
      sort_order AS SortOrder,
      name AS Name,
      description AS Description
    FROM
      `$tables.DataTypes` main
  }

@_getProcessesTable[]
  ^CSQL.table{
    SELECT
      main.process_id AS ID,
      main.sort_order AS SortOrder,
      main.process_type_id AS TypeID,
      main.dt_update AS DateUpdate,
      main.name AS Name,
      main.filename AS FileName,
      main.description AS Description
    FROM
      `$tables.Processes` main
  }

#----- Properties -----

@GET_CSQL[]
  ^if(!def $self._sql){
      $self._sql[^sqlFactory[^if(def $_createOptions.sqlConnectString){$_createOptions.sqlConnectString}{$MAIN:SQL.connect-string};$_createOptions.sqlOptions]]
  }
  $result[$_sql]

@GET_tablesPrefix[]
  $result[$_tablesPrefix]

@GET_tables[]
  $result[$_tables]

@GET_additionalObjectsFields[]
  $result[$_additionalFields.Objects]

@GET_additionalTextsFields[]
  $result[$_additionalFields.ObjectsTexts]

@GET_preloadBlocks[]
  $result[$self._preloadBlocks]

@SET_preloadBlocks[blnLoad]
  $self._preloadBlocks(^blnLoad.bool(false))

#----- Fabriques -----

@sqlFactory[strConnectString;hshSQLOptions]
  ^switch[^strConnectString.left(^strConnectString.pos[://])]{
    ^case[mysql]{
      ^use[pf/sql/pfMySQL.p]
      $result[^pfMySQL::create[$strConnectString;$hshSQLOptions]]
    }
    ^case[DEFAULT]{
      ^pfAssert:fail[pfSiteModule.sqlFactory. Bad connect-string.]
    }
  }
